#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite

#数据库文件
DATABASE="english.db"

class WordsDao(object):
  
    def __init__(self):
        pass 
    
    def toDbList(self,obj):
        """
        将二进制数据编码
        """
        tts_en=lite.Binary(obj["tts_en"]) if obj["tts_en"] else None
        tts_zh=lite.Binary(obj["tts_zh"]) if obj["tts_zh"] else None
        image=lite.Binary(obj["image"]) if obj["image"] else None
        return (obj["txt_en"],obj["txt_zh"],tts_en,tts_zh,image)  
            
        
    def insert(self,obj):
        """
        功能：插入一条记录
        参数：
            obj：数据对象，格式
                {
                 'txt_en':'apple',
                 'txt_zh':'苹果',
                 }
        """
        with lite.connect(DATABASE) as con:
            cur = con.cursor()
            cur.execute("INSERT INTO Words(txt_en,txt_zh) VALUES (?,?)",(obj["txt_en"],obj["txt_zh"]))

    def update(self,id,obj):
        """
        功能：更新一条记录
        参数：
            id：记录编号
            obj：数据对象
        """
        str="UPDATE Words SET txt_en=?,txt_zh=? WHERE id = %s" % (id,)
        con = lite.connect(DATABASE)
        with con:
            cur = con.cursor()
            cur.execute(str,(obj["txt_en"],obj["txt_zh"]))
    
    def load(self):
        """
        功能：读取数据库中的所有记录
        返回：字典对象数组
        """
        with lite.connect(DATABASE) as con:
            #使用字典对象游标，返回的数据记录可以用列名访问
            con.row_factory = lite.Row
            #获得游标
            cur = con.cursor()
            #读取所有记录
            cur.execute("SELECT id,txt_en,txt_zh From  Words")
            rows = cur.fetchall()
        return rows 


    def selectById(self,id):
        """
        功能：查找指定编号的单词
        参数编号
        返回：字典类型的单词对象
        """
        con = lite.connect(DATABASE)
        with con:
            con.row_factory = lite.Row#使用字典对象游标
            cur = con.cursor()
            cur.execute("SELECT * From  Words Where id=?  LIMIT 1",(id,))
            row = cur.fetchone()
        return row
    
    def selectByEnglish(self,en):
        """
        功能：查找指定单词
        参数：英文单词
        返回：字典类型的单词对象
        """
        con = lite.connect(DATABASE)
        with con:
            con.row_factory = lite.Row
            cur = con.cursor()
            cur.execute("SELECT * From  Words Where txt_en LIKE ?   LIMIT 1",(en,))
            row = cur.fetchone()
            
        return row
    
    def selectByChinese(self,zh):
        """
        功能：查找指定单词
        参数：中文单词
        返回：字典类型的单词对象
        """
        con = lite.connect(DATABASE)
        with con:
            con.row_factory = lite.Row
            cur = con.cursor()
            cur.execute("SELECT * From  Words Where txt_zh LIKE ?  LIMIT 1",(zh,))
            row = cur.fetchone()
        return row
    
